﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageAward_SearchVolunteerControl

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal Region As String, ByVal Region2 As String, ByVal VillageName As String, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal AwardID As Integer, ByVal hour As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT A.VolunteerID,A.VolunteerName,CASE WHEN B.VolunteerID is null THEN '1' ELSE '0' END as IsEnable, WorkHour ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("LEFT JOIN (SELECT VolunteerID FROM AwardRecord WHERE AwardID = {0}) B on A.VolunteerID = B.VolunteerID ", AwardID)
        sql.AppendFormat("LEFT JOIN Region C on A.VillageID=C.RegionID ")
        sql.AppendFormat("inner join VolunteerStat vs on A.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat("WHERE A.Active IN ('Y') ")
        sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (C.RegionName like '%{0}%' or '{0}'='') ", VillageName)
        sql.AppendFormat("AND (A.VolunteerId like '%{0}%' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' or '{0}'='') or (A.OfficePhone like '%{0}%' or '{0}'=''))) ", TEL)

        If hour <> "" Then
            sql.AppendFormat("and vs.WorkHour >= {0} ", hour)
        End If
        'MyObj.WriteLog(sql.ToString())
        sql.AppendFormat("ORDER BY IsEnable desc,A.VolunteerID asc")

        Return dat.GetData(sql)

    End Function

End Class
